<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Rates_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function insert_rates($data){
        $this->db->query("call ubs_rates_ins(?,?,?,?,?,? ,@id,@err,@msg)",$data);
        $que = $this->db->query("SELECT @id as id , @err as error , @msg as msg");
        echo json_encode($que->result());
    }
    public function update_rates($data){
        $this->db->query("call ubs_rates_upd(?,?,?,?,?,@err,@msg)",$data);
        $que = $this->db->query("SELECT @err as error , @msg as msg");
        echo json_encode($que->result());
    }
    public function search_rates($name,$desc){
        $sql;
          if($name != "" && $desc != ""){
            $sql = "SELECT * FROM ubs_rates WHERE rate_name LIKE ".$this->db->escape($name)." AND description LIKE '".$desc."' ORDER BY rate_name";
          }else if($name == "" && $desc != ""){
            $sql = "SELECT * FROM ubs_rates WHERE description LIKE ".$this->db->escape($desc)." ORDER BY rate_name ";
          }else{
            $sql = "SELECT * FROM ubs_rates WHERE rate_name LIKE ".$this->db->escape($name)." ORDER BY rate_name ";
          }
          //$query = $this->db->get_where('app_modules', array('module_code' => $modulecode, 'module_name' => $desc), 1);
          $query = $this->db->query($sql);
          if($query->num_rows() != 0){
            return $query->result();
          }
          else {
            $result = "No results found.";
            return $result;
          }
    }



    public function insert_rates_sched($data){
        $this->db->query("call ubs_rate_schedule_ins(?,?,?,?,?,?,@id,@err,@msg)",$data);
        $que = $this->db->query("SELECT @id as id , @err as error , @msg as msg");
        return $que->result();
    }
    public function update_inline_sched($data){
        $this->db->query("call ubs_rate_schedule_upd(?,?,?,?,?,?,@err,@msg)",$data);
        $que = $this->db->query("SELECT @err as error , @msg as msg");
        echo json_encode($que->result());
    }





    public function get_sched($id){
          $sql = "SELECT urs.line_id line_id, urs.rate_id rate_id, uce.charge_name charge_name, urs.description description, urs.usage_qty usage_qty, urs.rate rate , (SELECT(CASE WHEN urs.usage_qty = (SELECT min(usage_qty) FROM ubs_rate_schedule WHERE rate_id = '".$id."') THEN 'M' ELSE 'X' END)) MIN, urs.min_flag mf FROM ubs_rate_schedule urs INNER JOIN ubs_chg_elements uce ON uce.charge_code = urs.charge_code WHERE urs.rate_id = '".$id."' ";
       // $sql="SELECT urs.line_id line_id, urs.rate_id rate_id, uce.charge_name charge_name, urs.description description, urs.usage_qty usage_qty, urs.rate rate FROM ubs_rate_schedule urs INNER JOIN ubs_chg_elements uce ON uce.charge_code = urs.charge_code WHERE urs.rate_id = '".$id."' ";
        $query = $this->db->query($sql);
        return $query->result();
    }
    public function get_charges(){
      $sql = "SELECT * FROM ubs_chg_elements";
      $query = $this->db->query($sql);
      return $query->result();
    }    
    public function get_rates_data($id){
      //$this->db->where('rate_id', $id);
      //$query = $this->db->get('ubs_rates');
      $query = $this->db->query('SELECT *,(SELECT ucl.list_name FROM ubs_chg_list ucl WHERE ucl.list_id = ur.list_id ) list_name FROM ubs_rates ur WHERE ur.rate_id = '.$id);
      return $query->result();
    }

    public function getChargeList(){
      $q = $this->db->query('SELECT * FROM ubs_chg_list WHERE list_id NOT IN (SELECT list_id FROM ubs_rates)');
      return $q->result();
    }
}